
package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;

import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.model.FormatDefinition;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.service.Soez5002Service;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5002ServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/12 create by Jimmy Liu<br>
 */
@Service("soez5002Service")
public class Soez5002ServiceImpl extends BaseServiceImpl implements Soez5002Service {

    @Resource(name = "dataSource")
    private DataSource dataSource;
    
    @Resource(name="groupDao")
    private GroupDao groupDao;
    
    /**
     * 暫存目錄
     */
    @Resource(name = "tempDirectory")
    private String tempDirectory;
    //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
    private static final String[] titles = {
        "客戶編號", "繳款帳號", "客戶名稱", "是否逾繳", "繳費期限"
        , "繳款通路", "繳款狀況", "繳費日期", "銷帳日期", "超商代收編號"
        , "代收手續費(超商)", "帳務處理費(遠銀)", "應繳金額(1)", "實繳金額(1)", "入帳金額(1)"
        , "溢繳金額(1)", "應繳金額(2)", "實繳金額(2)", "入帳金額(2)", "溢繳金額(2)", "銷帳代碼", "銷帳原因", "備註"};
    //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題

    public String doExportXls(Payment payment, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo) throws Exception
    {
        
        Group dbGroup = groupDao.findByGroupNo(payment.getGroup().getGroupNo());
       
      
        logger.debug("payDueFrom="+payDueFrom);
        logger.debug("writeOffDateFrom="+writeOffDateFrom);
        logger.debug("txDateFrom="+txDateFrom);
        
        SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
        
        NumberFormat nf  = new DecimalFormat("$#,##0");
        
        // 繳費截止日期查詢條件設定
        if (null != payDueFrom)
        {
            // 將時分秒設定在00:00:00
            payDueFrom = DateUtil.getBeginOfDateTime(payDueFrom);
        }

        if (null != payDueTo)
        {
            // 將時分秒設定在23:59:59
            payDueTo = DateUtil.getEndOfDateTime(payDueTo);
        }
        
        // 銷帳日期查詢條件設定
        if (null != writeOffDateFrom)
        {
            // 將時分秒設定在00:00:00
            writeOffDateFrom = DateUtil.getBeginOfDateTime(writeOffDateFrom);
        }

        if (null != writeOffDateTo)
        {
            // 將時分秒設定在23:59:59
            writeOffDateTo = DateUtil.getEndOfDateTime(writeOffDateTo);
        }
        
        // 實際繳款日期查詢條件設定
        if (null != txDateFrom)
        {
            // 將時分秒設定在00:00:00
            txDateFrom = DateUtil.getBeginOfDateTime(txDateFrom);
        }

        if (null != txDateTo)
        {
            // 將時分秒設定在23:59:59
            txDateTo = DateUtil.getEndOfDateTime(txDateTo);
        }
        
        Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

        CreationHelper createHelper = wb.getCreationHelper();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = wb.createSheet("Timesheet");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);


        // 行數
        int rownum = 0;

        //title row
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(33);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("繳款狀況明細表");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$W$1"));

        // printDate row
        Row printDateRow = sheet.createRow(rownum++);
        printDateRow.setHeightInPoints((float) 22.5);
        Cell printDateCell = printDateRow.createCell(15);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        printDateCell.setCellValue("製表日期/時間：" + dateFormat.format(Calendar.getInstance().getTime()));
        printDateCell.setCellStyle(styles.get("title2"));
       //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
       //合併儲存格
        sheet.addMergedRegion(CellRangeAddress.valueOf("$P$2:$W$2"));
        
        // condition row 帳單業者名稱
        Row conditionRow = sheet.createRow(rownum++);
        conditionRow.setHeightInPoints((float) 22.5);
        Cell conditionGroupNameCell = conditionRow.createCell(0);
        conditionGroupNameCell.setCellValue("帳單業者："+dbGroup.getGroupName());
        conditionGroupNameCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$E$3"));

        // condition row 帳單業者代號
        Cell conditionGroupNoCell = conditionRow.createCell(5);
        conditionGroupNoCell.setCellValue("帳單業者代號 (統編+序號)："+payment.getGroup().getGroupNo());
        conditionGroupNoCell.setCellStyle(styles.get("title2"));
    
        sheet.addMergedRegion(CellRangeAddress.valueOf("$F$3:$I$3"));
      
        // condition row 資料期間     
      //  Cell conditionDataPeriodCell = conditionRow.createCell(14);
        Cell conditionDataPeriodCell = conditionRow.createCell(15);
        
        String sDataDuring = "";
        if(null != txDateFrom){
        	sDataDuring += "(1).繳費日期" + dateFormat1.format(txDateFrom)+ "~" +dateFormat1.format(txDateTo);        	
        }
        if (null != writeOffDateFrom){
        	sDataDuring += "(2).銷帳日期" + dateFormat1.format(writeOffDateFrom)+ "~" +dateFormat1.format(writeOffDateTo);
        }
        if (null != payDueFrom){
        	sDataDuring += "(3).繳費期限" + dateFormat1.format(payDueFrom)+ "~" +dateFormat1.format(payDueTo);	
        }
        conditionDataPeriodCell.setCellValue("資料期間：" + sDataDuring );
        
        conditionDataPeriodCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$P$3:$W$3"));
               
       //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
       // sheet.addMergedRegion(CellRangeAddress.valueOf("$O$3:$Q$3"));
       
        // condition row 檔案名稱     
     //   Cell conditionFileNameCell = conditionRow.createCell(17);
     //   conditionFileNameCell.setCellValue("檔案名稱：QAV_20101201.TXT");
      //  conditionFileNameCell.setCellStyle(styles.get("title2"));;
     //   sheet.addMergedRegion(CellRangeAddress.valueOf("$R$3:$T$3"));

        //明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。
        Row sumaryRow = sheet.createRow(rownum++);
        sumaryRow.setHeightInPoints((float) 22.5);
        Cell sumaryCell = sumaryRow.createCell(0);
        sumaryCell.setCellValue("明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。");
        sumaryCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$4:$N$4"));

        //header row
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints((float) 34.5);
        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }


        List<Object> sqlArgs = new ArrayList<Object>();
        String sql = getSql(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo, sqlArgs);
        logger.debug("匯出EXCEL SQL:{}", sql);
        logger.debug("匯出EXCEL 參數:{}", sqlArgs);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        XlsResultSetExtractor extrator = new XlsResultSetExtractor(sheet, rownum, styles, dataSource);
        Object nullObject = jdbcTemplate.query(sql, sqlArgs.toArray(), extrator);        

        rownum = extrator.getRownum();

        Map<String, Long> calculateMap = extrator.getCalculateMap();
        sumaryCell.setCellValue("明細小計：已繳款 "+calculateMap.get("Paid")+"筆，金額 "+nf.format(calculateMap.get("Total"))
                        +"元 / 未繳款 "+calculateMap.get("unPaid")
                        +"筆，金額 "+((Long)calculateMap.get("TotalUnPayValue").longValue()<0 ? 0 : nf.format(calculateMap.get("TotalUnPayValue")))+"元 。");
        
        // adjust the column widths
        int colCount = 0;
        //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題 
        //titles.length == 欄位數
        while (colCount <= titles.length) 
        {
            sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
        }
        //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
        logger.debug("[繳款狀況明細表]暫存目錄:{}", tempDownloadFilePath);
        File directory = new File(tempDownloadFilePath);             
        if(!directory.exists())
        {
            FileUtils.forceMkdir(directory);
        }
        
        String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-繳款狀況明細表.xls";
        logger.debug("[繳款狀況明細表]:{}", fileFullName);
        
        FileOutputStream out;
        out = new FileOutputStream(fileFullName);
        wb.write(out);
        out.flush();
        out.close();
        
        return fileFullName;
    }



    /**
     * @return
     */
    private String getSql(Payment payment, FormatDefinition formatDefinition
                    , Date payDueFrom, Date payDueTo
                    , Date writeOffDateFrom, Date writeOffDateTo
                    , Date txDateFrom, Date txDateTo
                    , List<Object> args) 
    {
        args.clear();
        StringBuffer sql = new StringBuffer();

        sql.append(" SELECT");
        sql.append(" PAYMENT.PAYMENT_PK");
        sql.append(" , FORMAT_DEFINITION.FORMAT_DEF_PK");
        sql.append(" , PAYMENT.STUDENT_ID");
        sql.append(" , PAYMENT.PAYER_ID ");//客戶編號
        sql.append(" , PAYMENT.VIRTUAL_ACCOUNT ");//繳款帳號
        sql.append(" , PAYMENT.PAYER_NAME ");//客戶名稱
        sql.append(" , PAYMENT.PAY_DUE ");//繳款截止日
        sql.append(" , PAYMENT.PAYMENT_STATUS ");//繳款狀況
        sql.append(" , PAYMENT.WRITE_OFF_PAYMENT_PK");
        sql.append(" , WRITE_OFF_PAYMENT.CHANNEL ");//繳款通路
        sql.append(" , WRITE_OFF_PAYMENT.TX_DATE ");//繳款交易日
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_DATE ");//繳款核銷日
        //sql.append(" -- 超商代收類別");
        //sql.append(" -- 代收手續費(超商)");
        //sql.append(" -- 帳務處理費(遠銀)");
        sql.append(" , PAYMENT.TOTAL_AMOUNT ");//應繳金額(1)
        sql.append(" , PAYMENT.TOTAL_LOANABLE_AMOUNT ");//應繳金額(2)
        sql.append(" , WRITE_OFF_PAYMENT.AMOUNT ");//實繳金額(1/2)
        /* 銷帳代碼 */
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_NO");
        /* 銷帳原因 */
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_NOTE");
        /* 備註 */
        sql.append(" , PAYMENT.PAYMENT_NOTE_3 ");
        /* 註銷時間 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_DATE ");
        /* 註銷代碼 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_NO ");
        /* 註銷原因 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_REASON ");
        sql.append(" , PAYMENT.BARCODE_STORES_1");
        sql.append(" , PAYMENT.BARCODE_STORES_1_LOAN");
        sql.append(" , APP_GROUP.GROUP_NO");
        sql.append(" , APP_GROUP.GROUP_NAME");
        sql.append(" , APP_GROUP.GROUP_TYPE");
        sql.append(" , APP_GROUP.BANK_AMOUNT");
        sql.append(" , APP_GROUP.POST_AMOUNT");       
        sql.append(" , APP_GROUP.ATM_AMOUNT");
        sql.append(" , APP_GROUP.REMITTANCE_AMOUNT");
        sql.append(" , APP_GROUP.OTHER_AMOUNT");  
        sql.append(" , APP_GROUP.CHAINSTORE_AMOUNT");
        sql.append(" , APP_GROUP.POST_LEVEL1");
        sql.append(" , APP_GROUP.POST_LEVEL2");
        sql.append(" , APP_GROUP.POST_LEVEL3");
        sql.append(" , APP_GROUP.SELF_AMOUNT "); /* 20120828 自行繳款之「帳務處理費」計算   */
        sql.append(" , CODE_TABLE_CHANNEL.CODE_NAME AS CHANNEL_NAME"); //繳款通路中文
        sql.append(" , CODE_TABLE_PAYMENT_STATUS.CODE_NAME AS PAYMENT_STATUS_NAME"); //繳款通路中文
        sql.append(" FROM PAYMENT LEFT JOIN WRITE_OFF_PAYMENT ON PAYMENT.WRITE_OFF_PAYMENT_PK = WRITE_OFF_PAYMENT.WRITE_OFF_PAYMENT_PK");
        /* 註銷表 */
        sql.append(" LEFT JOIN PAYMENT_CANCELED ON PAYMENT.PAYMENT_CANCELED_PK = PAYMENT_CANCELED.PAYMENT_CANCELED_PK ");
        sql.append(" INNER JOIN APP_GROUP ON PAYMENT.GROUP_PK = APP_GROUP.GROUP_PK");
        sql.append(" INNER JOIN UPLOAD_FILE ON PAYMENT.UPLOAD_FILE_PK = UPLOAD_FILE.UPLOAD_FILE_PK");
        sql.append(" INNER JOIN FORMAT_DEFINITION ON UPLOAD_FILE.FORMAT_DEF_PK = FORMAT_DEFINITION.FORMAT_DEF_PK ");
        sql.append(" LEFT JOIN CODE_TABLE CODE_TABLE_CHANNEL ON CODE_TABLE_CHANNEL.CODE_KIND='CHANEL' AND CODE_TABLE_CHANNEL.CODE_VALUE = WRITE_OFF_PAYMENT.CHANNEL");
        sql.append(" LEFT JOIN CODE_TABLE CODE_TABLE_PAYMENT_STATUS ON CODE_TABLE_PAYMENT_STATUS.CODE_KIND='PAYMENT_STATUS' AND CODE_TABLE_PAYMENT_STATUS.CODE_VALUE = PAYMENT.PAYMENT_STATUS");
        sql.append(" WHERE 1= 1 ");

        if (null != payment)
        {
            if (StringUtils.isNotEmpty(payment.getVirtualAccount()))
            {
                sql.append("AND PAYMENT.VIRTUAL_ACCOUNT LIKE ? ");
                args.add(payment.getVirtualAccount() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getPayerName()))
            {
                sql.append("AND PAYMENT.PAYER_NAME LIKE ? ");
                args.add(payment.getPayerName() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getPayerId()))
            {
                sql.append("AND PAYMENT.PAYER_ID LIKE ? ");
                args.add(payment.getPayerId() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getStudentId()))
            {
                sql.append("AND PAYMENT.STUDENT_ID LIKE ? ");
                args.add(payment.getStudentId() + "%");
            }

            if (null != payment.getPayDue()) //單一日期
            {
                sql.append("AND PAYMENT.PAY_DUE between ? and ? ");
                Date from = DateUtil.getBeginOfDateTime(payment.getPayDue());
                Date to = DateUtil.getEndOfDateTime(payment.getPayDue());
                args.add(from);
                args.add(to);
            }
        }        


        // 繳費截止日期
        if (null != payDueFrom && null != payDueTo)
        {
            sql.append("AND PAYMENT.PAY_DUE between ? and ? ");
            args.add(payDueFrom);
            args.add(payDueTo);
        }
        else if (null != payDueFrom) // 只有起
        {
            sql.append("AND PAYMENT.PAY_DUE >= ? ");
            args.add(payDueFrom);
        }
        else if (null != payDueTo) // 只有迄
        {
            sql.append("AND PAYMENT.PAY_DUE <= ? ");
            args.add(payDueTo);
        }


        // 銷帳日期
        // 有起迄
        if (null != writeOffDateFrom && null != writeOffDateTo)
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE between ? and ? ");
            args.add(writeOffDateFrom);
            args.add(writeOffDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE between ? and ? ) ");
            args.add(writeOffDateFrom);
            args.add(writeOffDateTo);

        }
        else if (null != writeOffDateFrom) // 只有起
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE >= ? ");
            args.add(writeOffDateFrom);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE >= ? ) ");
            args.add(writeOffDateFrom);
        }
        else if (null != writeOffDateTo) // 只有迄
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE <= ? ");
            args.add(writeOffDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE <= ? ) ");
            args.add(writeOffDateTo);
        }

        // 實際繳款日期
        // 有起迄
        if (null != txDateFrom && null != txDateTo)
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE between ? and ? ");
            args.add(txDateFrom);
            args.add(txDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);

        }
        else if (null != txDateFrom) // 只有起
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE >= ? ");
            args.add(txDateFrom);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
        }
        else if (null != txDateTo) // 只有迄
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE <= ? ");
            args.add(txDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
        }

        // 收款類別
        if (null != formatDefinition)
        {
            if (null != formatDefinition.getFormatDefinitionPk())
            {
                sql.append("AND FORMAT_DEFINITION.FORMAT_DEF_PK = ? ");
                args.add(formatDefinition.getFormatDefinitionPk());
            }
        }
        

        // 繳費狀態，這裡多了一個未繳款狀態
        if (null != payment.getPaymentStatus())
        {
            if (1 == payment.getPaymentStatus().intValue())
            {
                // 未放行資料
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_WAIT_CONFIRM);
            }
            else if (2 == payment.getPaymentStatus().intValue())
            {
                // 待繳款，這裡的代繳款是指 未繳且未超過繳費期限
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_IN_PAYING);
                
                // 排除逾期
                Date from = DateUtil.getBeginOfDateTime(new Date());
                sql.append("AND PAYMENT.PAY_DUE >= ? ");
                args.add(from);
                
            }
            else if (3 == payment.getPaymentStatus().intValue())
            {
                // 註銷中
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_IN_CANCELING);
            }
            else if (4 == payment.getPaymentStatus().intValue())
            {
                // 已註銷
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_CANCELED);
            }
            else if (5 == payment.getPaymentStatus().intValue())
            {
                // 已繳款
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_CLOSED);
            }
            else if (6 == payment.getPaymentStatus().intValue())
            {
                // 逾期 = 未繳款 且 超過繳費期限
                sql.append("AND PAYMENT.PAYMENT_STATUS = ? ");
                args.add(Payment.PAYMENT_STATUS_IN_PAYING);
                
                Date to = DateUtil.getBeginOfDateTime(new Date());
                sql.append("AND PAYMENT.PAY_DUE < ? ");
                args.add(to);
            }
            
        }
        else
        {
            // 排除未放行資料
            sql.append("AND PAYMENT.PAYMENT_STATUS <> ? ");
            args.add(new Integer(1));
        }

        logger.debug("sql.toString() {}",sql.toString());
        return sql.toString();
        
    }




    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontName("標楷體");
        titleFont.setFontHeightInPoints((short)24);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font title2Font = wb.createFont();
        title2Font.setFontName("標楷體");
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        title2Font.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(title2Font);    
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styles.put("title2", style);

        Font headerFont = wb.createFont();
        headerFont.setFontName("標楷體");
        headerFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(headerFont);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("header", style);

        Font cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);
        
        style = wb.createCellStyle();
        Font cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number", style);
        
        style = wb.createCellStyle();
        Font cellDateFont = wb.createFont();
        cellDateFont.setFontName("Arial");
        cellDateFont.setFontHeightInPoints((short)12);
        style.setFont(cellDateFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
        styles.put("cell_date", style);
        
        

        return styles;
    }


    class XlsResultSetExtractor implements ResultSetExtractor<Object>
    {

        int rownum = 0;
        int Paid=0;
        int unPaid=0;
        Sheet sheet = null;
        Map<String, CellStyle> styles;

        DataSource dataSource;

        Map<Long, List<Map<String, Object>>> commissionMap = new HashMap<Long, List<Map<String,Object>>>();

         Map<String, Long> calculateMap = new HashMap<String,Long>(); 

        SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
        String today = sd.format(Calendar.getInstance().getTime());

        public XlsResultSetExtractor(Sheet sheet, int starRownum, Map<String, CellStyle> styles, DataSource dataSource)
        {
            this.sheet = sheet;
            this.rownum = starRownum;
            this.styles = styles;
            this.dataSource = dataSource;
        }

        protected Map<String, Object> getCommission(String commissionCode, Long formatDefPk)
        {
        	 
            List<Map<String, Object>> commissionCodes = null;   
            commissionMap.clear();
            
            if (null == commissionCodes)
            {
                // 到資料庫抓取
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                StringBuffer sql = new StringBuffer();
                sql.append("select ");
                sql.append("  COMMISSION_CODE_TABLE.COMMISSION_CODE_PK");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE_NAME");
                sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_S");
                sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_E");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_TYPE");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_AMOUNT");
                sql.append(", COMMISSION_CODE_TABLE.BANK_AMOUNT");
                sql.append(", COMMISSION_CODE_TABLE.PAYER_AMOUNT");
                sql.append(", COMMISSION_CODE_TABLE.GROUP_AMOUNT");                
                sql.append(" from COMMISSION_CODE_TABLE");               
                sql.append(" where COMMISSION_CODE_TABLE.COMMISSION_CODE = ?");
                commissionCodes = jdbcTemplate.queryForList(sql.toString(), new Object[]{commissionCode});
                commissionMap.put(formatDefPk, commissionCodes);
            }
            
            if (commissionCodes.size()== 1){
            	for(Map<String, Object> result : commissionCodes) {
                    return result;
                }
            }
            return null;              
        }

        /* (non-Javadoc)
         * @see org.springframework.jdbc.core.ResultSetExtractor#extractData(java.sql.ResultSet)
         */
        @Override
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            long kTotalValue = 0;
            long lTotalValue = 0;
            long mTotalValue = 0;
            long nTotalValue = 0;
            long oTotalValue = 0;
            long pTotalValue = 0;
            long qTotalValue = 0;
            long rTotalValue = 0;
            long sTotalValue = 0;
            long tTotalValue = 0;
            while (rs.next()) {

                // data rows

                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints((float) 24);
                int j = 0;
                

                // 客戶編號
                String aValue = rs.getString("PAYER_ID");
                Cell acell = row.createCell(j++);
                acell.setCellValue(aValue);
                acell.setCellStyle(styles.get("cell"));

                // 繳款帳號
                String bValue = rs.getString("VIRTUAL_ACCOUNT");
                Cell bcell = row.createCell(j++);
                bcell.setCellValue(bValue);
                bcell.setCellStyle(styles.get("cell"));

                // 客戶名稱
                String cValue = rs.getString("PAYER_NAME");
                Cell ccell = row.createCell(j++);
                ccell.setCellValue(cValue);
                ccell.setCellStyle(styles.get("cell"));

                // 是否逾繳
                String dValue = "否";
                Date eValue = rs.getDate("PAY_DUE"); //繳款截止日
                Date hValue = rs.getDate("TX_DATE");  //實際繳款日期
                int payMentStatus = rs.getInt("PAYMENT_STATUS");
                String payDueStr = sd.format(eValue);
                if (null != hValue)
                {
                    String writeOffDateStr = sd.format(hValue);
                    if (writeOffDateStr.compareTo(payDueStr) > 0)
                    {
                        dValue = "是"; //是否逾繳
                    }
                }
                else if (Payment.PAYMENT_STATUS_IN_PAYING.intValue() == payMentStatus)
                {
                    if (today.compareTo(payDueStr) > 0)
                    {
                        dValue = "是"; //是否逾繳
                    }
                }

                Cell dcell = row.createCell(j++);
                dcell.setCellValue(dValue);
                dcell.setCellStyle(styles.get("cell"));

                // 繳款截止日
                //Date eValue = rs.getDate("PAY_DUE");
                Cell ecell = row.createCell(j++);
                if (null != eValue)
                    ecell.setCellValue(eValue);
                ecell.setCellStyle(styles.get("cell_date"));

                String channel = rs.getString("CHANNEL");
                boolean isStore = WriteOffPayment.CHANNEL_7_11.equals(channel) || WriteOffPayment.CHANNEL_FAMILY_MART.equals(channel)
                    || WriteOffPayment.CHANNEL_OK_MART.equals(channel) || WriteOffPayment.CHANNEL_HILIFE.equals(channel);
                
                boolean isPost = WriteOffPayment.CHANNEL_POST.equals(channel); 
                // 繳款通路
                String fValue = rs.getString("CHANNEL_NAME");
                Cell fcell = row.createCell(j++);
                fcell.setCellValue(fValue);
                fcell.setCellStyle(styles.get("cell"));

                // 繳款狀況
                String gValue = rs.getString("PAYMENT_STATUS_NAME");
                Cell gcell = row.createCell(j++);
                gcell.setCellValue(gValue);
                gcell.setCellStyle(styles.get("cell"));

                // 繳款交易日
                //Date hValue = rs.getDate("WRITE_OFF_DATE");
                Cell hcell = row.createCell(j++);
                if (null != hValue)
                    hcell.setCellValue(hValue);
                hcell.setCellStyle(styles.get("cell_date"));

                // 繳款核銷日
                Date iValue = null;
                if( Payment.PAYMENT_STATUS_CANCELED.intValue() == payMentStatus){
                	iValue = rs.getDate("CANCEL_DATE");
                }else{
                	iValue = rs.getDate("WRITE_OFF_DATE");
                }
                Cell icell = row.createCell(j++);
                if (null != iValue)
                    icell.setCellValue(iValue);
                icell.setCellStyle(styles.get("cell_date"));

                int paymentStatus = rs.getInt("PAYMENT_STATUS");
                long amount = rs.getLong("AMOUNT"); // 實繳金額
                long totalAmount = rs.getLong("TOTAL_AMOUNT"); //應繳金額1
                long totalLoanableAmount = rs.getLong("TOTAL_LOANABLE_AMOUNT");// 應繳金額2

                boolean onlyOneAmount = (totalLoanableAmount == 0); // 帳單只有一個金額
                int closeBySWhichAmount = 0; // default 未繳款, 1: 第一個金額, 2:第二個金額

                String barCode = null;
                String commissionCode = null;
                Map<String, Object> commissionRecord = null;

                // 是否已繳款
                if ( Payment.PAYMENT_STATUS_CLOSED.intValue() == paymentStatus)
                {
                    Paid++;
                    // 已繳款
                    if (amount == totalLoanableAmount)
                    {
                        // 用第二個金額/貸款後金額繳款
                        closeBySWhichAmount = 2;
                        barCode = rs.getString("BARCODE_STORES_1_LOAN");
                    }
                    else
                    {
                        // 用原始金額繳款
                        closeBySWhichAmount = 1;
                        barCode = rs.getString("BARCODE_STORES_1");
                    }
                }
                else
                {
                    unPaid++;
                    // 尚未繳款

                    // 用第一個金額
                    barCode = rs.getString("BARCODE_STORES_1");
                }

                if (barCode != null) {                	
                    commissionCode = barCode.substring(6); //000215AFF==>AFF                    
                }else{
                	/* 超過繳款級距，條碼為空白 */
                	commissionCode = "";
                }
                Long formatDefPk = rs.getLong("FORMAT_DEF_PK");
                commissionRecord = this.getCommission(commissionCode, formatDefPk);                

                //繳款通路
            
                long kValue = 0; // 代收手續費(超商)
                if(isStore){
                    long commionAmount = 0;
                    long payerAmount = 0;
                    long groupAmount = 0;                    
                    if (commissionRecord != null){
	                    if (null != commissionRecord.get("COMMISSION_AMOUNT"))
	                        commionAmount = Long.parseLong(String.valueOf(commissionRecord.get("COMMISSION_AMOUNT")) );
	                    if (null != commissionRecord.get("PAYER_AMOUNT"))
	                        payerAmount = Long.parseLong(String.valueOf(commissionRecord.get("PAYER_AMOUNT")) ); 
	                    if (null != commissionRecord.get("GROUP_AMOUNT"))
	                        groupAmount = Long.parseLong(String.valueOf(commissionRecord.get("GROUP_AMOUNT")) );	                    
	                    if (commissionRecord.get("COMMISSION_TYPE").equals("I")){
	                    	/* 20120130 手續費類別(內含): 代收手續費 邏輯 =(超商成本) */
	                    	kValue = commionAmount;
	                    }else if (commissionRecord.get("COMMISSION_TYPE").equals("O")) {
	                    	/* 20120130 手續費類別(外加): 代收手續費 邏輯 =(超商成本) - (繳款者負擔) */	                    	
	                    	kValue = commionAmount - payerAmount;
	                    	if (kValue < 0)  kValue = 0;
	                    }else{
	                    	kValue = 0;
	                    	logger.debug("COMMISSION_TYPE:{}, not found.", commissionRecord.get("COMMISSION_TYPE"));
	                    }
                    }else{
                    	/* 帳單有此超商代號，但超商代號已不存在資料庫 */
                    	kValue = 0;
                    	logger.debug("COMMISSION_CODE:{}, not found.", commissionCode);
                    }
                }else if (isPost){
                	/* 郵局手續費 */
                	if (rs.getLong("AMOUNT") > 0  && rs.getLong("AMOUNT") <= 100 ){
                		kValue = rs.getLong("POST_LEVEL1");
                	}else if (rs.getLong("AMOUNT") > 100  && rs.getLong("AMOUNT") <= 1000) {
                		kValue = rs.getLong("POST_LEVEL2");
                	}else if (rs.getLong("AMOUNT") > 1000) {
                		kValue = rs.getLong("POST_LEVEL3");
                	}else{
                		kValue = 0;
                	}
                }
                
                long lValue = 0; // 帳務處理費(遠銀)
                if (channel != null && Payment.PAYMENT_STATUS_CLOSED.intValue() == paymentStatus){//已繳款才算
	                if (channel.equals("D") || channel.equals("E") || channel.equals("F") || channel.equals("G")){
	                    lValue = rs.getLong("CHAINSTORE_AMOUNT");
	                }else if (channel.equals("1") || channel.equals("2") || channel.equals("3") || channel.equals("4")){
	                    lValue = rs.getLong("ATM_AMOUNT");
	                }else if (channel.equals("0")){
	                	lValue = rs.getLong("BANK_AMOUNT");
	                }else if (channel.equals("8")){
	                	lValue = rs.getLong("REMITTANCE_AMOUNT");	
	                }else if (channel.equals("H")){
	                	lValue = rs.getLong("POST_AMOUNT");
	                }/* 20120828 自行繳款之「帳務處理費」計算   */
	                else if (channel.equals("I")){
	                	lValue = rs.getLong("OTHER_AMOUNT");
	                }
	                else if (channel.equals("Z")){
	                	lValue = rs.getLong("SELF_AMOUNT");
	                }
                }else{
                	lValue = 0;
                }
                // 超商代收類別
                String jValue="";
                if (commissionRecord != null){
	                if(isStore){
	                    jValue = (String)commissionRecord.get("COMMISSION_CODE"); //０４１９改超商代收類別代號
	                }
                }else{
                	jValue = commissionCode;
                }
                Cell jcell = row.createCell(j++);
                jcell.setCellValue(jValue);
                jcell.setCellStyle(styles.get("cell"));

                // 代收手續費(超商)
                //long kValue = rs.getLong("");
                Cell kcell = row.createCell(j++);
                if (kValue > 0)
                {
                    kcell.setCellValue(kValue);
                    kTotalValue = kTotalValue + kValue;
                }
                else
                    kcell.setCellValue("-");

                kcell.setCellStyle(styles.get("cell_number"));

                // 帳務處理費(遠銀)
                //long lValue = rs.getLong("");
                Cell lcell = row.createCell(j++);
                if (lValue > 0)
                {
                    lcell.setCellValue(lValue);
                    lTotalValue = lTotalValue + lValue;
                }
                else
                    lcell.setCellValue("-");
                lcell.setCellStyle(styles.get("cell_number"));

                // 應繳金額(1)
                long mValue = totalAmount;
                Cell mcell = row.createCell(j++);
                if (mValue > 0)
                {
                    mcell.setCellValue(mValue);
                    mTotalValue = mTotalValue + mValue;
                }
                else
                    mcell.setCellValue("-");
                mcell.setCellStyle(styles.get("cell_number"));

                // 實繳金額(1)
                long nValue = 0;
                Cell ncell = row.createCell(j++);
                if (closeBySWhichAmount == 1) // 已繳款且用第一個金額繳款
                {
                    nValue = amount;
                    if (0 < nValue)
                    {
                        ncell.setCellValue(nValue);
                        nTotalValue = nTotalValue + nValue;
                    }
                    else
                        ncell.setCellValue("-");
                }
                else
                    ncell.setCellValue("-");
                ncell.setCellStyle(styles.get("cell_number"));


                // 入帳金額(1) 
                long oValue = 0;                
                Cell ocell = row.createCell(j++);
                if (closeBySWhichAmount == 1)  // 已繳款且用第一個金額繳款
                {
                    // 入帳金額(1) = 應繳金額(1) - (代收手續費(超商)+ 帳務處理費 )
                    oValue = mValue -(lValue + kValue); //rs.getLong("");
                    if (oValue > 0 )
                    {
                        ocell.setCellValue(oValue);
                        oTotalValue = oTotalValue + oValue;
                    }
                }
                else
                    ocell.setCellValue("-");
                ocell.setCellStyle(styles.get("cell_number"));

                //溢繳金額(1)
                Cell pcell = row.createCell(j++);
                long pValue = 0;
                if (closeBySWhichAmount == 1)  // 已繳款且用第一個金額繳款
                {
                    //溢繳金額(1) = 實繳金額(1) - 應繳金額(1);
                    pValue = nValue - mValue;
                    if (pValue != 0 )
                    {
                        pcell.setCellValue(pValue);
                        pTotalValue = pTotalValue + pValue;
                    }
                    else
                        pcell.setCellValue("-");
                }
                else
                    pcell.setCellValue("-");
                pcell.setCellStyle(styles.get("cell_number"));

                // 應繳金額(2)
                Cell qcell = row.createCell(j++);
                long qValue = 0;
                if (closeBySWhichAmount == 2 || (!onlyOneAmount && Payment.PAYMENT_STATUS_CLOSED.intValue() != paymentStatus))  // 已繳款且用第2個金額繳款 或 (有兩個金額且未繳款)
                {
                    qValue = totalLoanableAmount;
                    if (qValue > 0)
                    {
                        qcell.setCellValue(qValue);
                        qTotalValue = qTotalValue + qValue;
                    }
                    else
                        qcell.setCellValue("-");
                }
                else
                    qcell.setCellValue("-");
                qcell.setCellStyle(styles.get("cell_number"));

                // 實繳金額(2)
                long rValue = 0;
                Cell rcell = row.createCell(j++);
                if (closeBySWhichAmount == 2 )  // 已繳款且用第2個金額繳款
                {
                    rValue = amount;
                    if (rValue > 0)
                    {
                        rcell.setCellValue(rValue);
                        rTotalValue = rTotalValue + rValue;
                    }
                    else
                        rcell.setCellValue("-");
                }
                else
                    rcell.setCellValue("-");
                rcell.setCellStyle(styles.get("cell_number"));


                // 入帳金額(2) 
                long sValue = 0;                
                Cell scell = row.createCell(j++);
                if (closeBySWhichAmount == 2)  // 已繳款且用第2個金額繳款
                {
                    // 入帳金額(2) = 應繳金額(2) - (代收手續費(超商)+ 帳務處理費 )
                    sValue = qValue -(lValue + kValue); //rs.getLong("");
                    if (sValue > 0 )
                    {
                        scell.setCellValue(sValue);
                        sTotalValue = sTotalValue + sValue;
                    }
                }
                else
                    scell.setCellValue("-");
                scell.setCellStyle(styles.get("cell_number"));

                //溢繳金額(2)
                Cell tcell = row.createCell(j++);
                long tValue = 0;
                if (closeBySWhichAmount == 2)  // 已繳款且用第2個金額繳款
                {
                    //溢繳金額(2) = 實繳金額(2) - 應繳金額(2);
                    tValue = rValue - qValue;
                    if (tValue != 0 )
                    {
                        tcell.setCellValue(tValue);
                        tTotalValue = tTotalValue + tValue;
                    }
                    else
                        tcell.setCellValue("-");
                }
                else
                    tcell.setCellValue("-");
                tcell.setCellStyle(styles.get("cell_number"));

                /* 銷帳代號 */
                String uValue = null;
                if( Payment.PAYMENT_STATUS_CANCELED.intValue() == payMentStatus){
                	uValue = rs.getString("CANCEL_NO");
                }else{
                	uValue = rs.getString("WRITE_OFF_NO");
                }
                Cell ucell = row.createCell(j++);
                ucell.setCellValue(uValue);
                ucell.setCellStyle(styles.get("cell"));
                
                /* 銷帳原因 */               
                String vValue = rs.getString("WRITE_OFF_NOTE");
                if( Payment.PAYMENT_STATUS_CANCELED.intValue() == payMentStatus){
                	vValue = rs.getString("CANCEL_REASON");
                }else{
                	vValue = rs.getString("WRITE_OFF_NOTE");
                }
                Cell vcell = row.createCell(j++);
                vcell.setCellValue(vValue);
                vcell.setCellStyle(styles.get("cell"));
                
                /* 備註 */
                String wValue = rs.getString("PAYMENT_NOTE_3");
                Cell wcell = row.createCell(j++);
                wcell.setCellValue(wValue);
                wcell.setCellStyle(styles.get("cell"));
            }

            // 小計     
            Row subTotalRolw = sheet.createRow(rownum++);
            Cell subTotalCell = subTotalRolw.createCell(0);
            subTotalCell.setCellValue("小計 ");
            subTotalCell.setCellStyle(styles.get("cell")); //TODO 要改
            for(int i=1;i<=9;i++){
            subTotalCell = subTotalRolw.createCell(i);
            subTotalCell.setCellStyle(styles.get("cell")); 
            }
            subTotalCell.setCellStyle(styles.get("cell")); 
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$J$"+rownum)); // ex: $A$3:$J$3


            int j = 10;
            // 小計: 代收手續費(超商)
            Cell kSubTotalCell = subTotalRolw.createCell(j++);
            if (kTotalValue > 0)
            {
                kSubTotalCell.setCellValue(kTotalValue);
            }
            else
                kSubTotalCell.setCellValue("-");
            kSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 帳務處理費(遠銀)
            Cell lSubTotalCell = subTotalRolw.createCell(j++);
            if (lTotalValue > 0)
            {
                lSubTotalCell.setCellValue(lTotalValue);
            }
            else
                lSubTotalCell.setCellValue("-");
            lSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 應繳金額(1)
            Cell mSubTotalCell = subTotalRolw.createCell(j++);
            if (mTotalValue > 0)
            {
                mSubTotalCell.setCellValue(mTotalValue);
            }
            else
                mSubTotalCell.setCellValue("-");
            mSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改

            // 小計: 實繳金額(1)
            Cell nSubTotalCell = subTotalRolw.createCell(j++);
            if (nTotalValue > 0)
            {
                nSubTotalCell.setCellValue(nTotalValue);
            }
            else
                nSubTotalCell.setCellValue("-");
            nSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改

            // 小計: 入帳金額(1)
            Cell oSubTotalCell = subTotalRolw.createCell(j++);
            if (oTotalValue > 0)
            {
                oSubTotalCell.setCellValue(oTotalValue);
            }
            else
                oSubTotalCell.setCellValue("-");
            oSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 溢繳金額(1)
            Cell pSubTotalCell = subTotalRolw.createCell(j++);
            if (pTotalValue > 0)
            {
                pSubTotalCell.setCellValue(pTotalValue);
            }
            else
                pSubTotalCell.setCellValue("-");
            pSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 應繳金額(2)
            Cell qSubTotalCell = subTotalRolw.createCell(j++);
            if (qTotalValue > 0)
            {
                qSubTotalCell.setCellValue(qTotalValue);
            }
            else
                qSubTotalCell.setCellValue("-");
            qSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 實繳金額(2)
            Cell rSubTotalCell = subTotalRolw.createCell(j++);
            if (rTotalValue > 0)
            {
                rSubTotalCell.setCellValue(rTotalValue);
            }
            else
                rSubTotalCell.setCellValue("-");
            rSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改

            // 小計: 入帳金額(2)
            Cell sSubTotalCell = subTotalRolw.createCell(j++);
            if (sTotalValue > 0)
            {
                sSubTotalCell.setCellValue(sTotalValue);
            }
            else
                sSubTotalCell.setCellValue("-");
            sSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改


            // 小計: 溢繳金額(2)
            Cell tSubTotalCell = subTotalRolw.createCell(j++);
            if (tTotalValue > 0)
            {
                tSubTotalCell.setCellValue(tTotalValue);
            }
            else
                tSubTotalCell.setCellValue("-");
            tSubTotalCell.setCellStyle(styles.get("cell_number")); //TODO 要改

            /* 保持匯出樣式 For 小計*/
            /* 小計:銷帳代碼 */
            Cell ucell = subTotalRolw.createCell(j++);
            ucell.setCellValue("");
            ucell.setCellStyle(styles.get("cell"));
            
            /* 小計:銷帳原因 */
            Cell vcell = subTotalRolw.createCell(j++);
            vcell.setCellValue("");
            vcell.setCellStyle(styles.get("cell"));
            
            /* 小計:備註 */
            Cell wcell = subTotalRolw.createCell(j++);
            wcell.setCellValue("");
            wcell.setCellStyle(styles.get("cell"));
            
            //明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。
            //應繳一+應繳二
            Long totalPayValue = nTotalValue + rTotalValue;
            Long totalUnPayValue = mTotalValue - nTotalValue;
            
            calculateMap.put("Total",totalPayValue);
            calculateMap.put("TotalUnPayValue",totalUnPayValue);
            //已付賬 
            calculateMap.put("Paid",(long)Paid);
            //位付賬 
            calculateMap.put("unPaid",(long)unPaid);
              
            
            
           
            
            return null;
            
            
          
            
        }


        /**
         * @return the rownum
         */
        public int getRownum() {
            return rownum;
        }

       

        /**
         * @return the calculateMap
         */
        Map<String, Long> getCalculateMap() {
            return calculateMap;
        }
    }


	@Override
	public String doExportDelayXls(Payment payment,
			FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo,
			Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom,
			Date txDateTo, boolean isSchool, int delayCount) throws Exception {
		// TODO Auto-generated method stub
		 Group dbGroup = groupDao.findByGroupNo(payment.getGroup().getGroupNo());
	       
	      
	        logger.debug("payDueFrom="+payDueFrom);
	        logger.debug("writeOffDateFrom="+writeOffDateFrom);
	        logger.debug("txDateFrom="+txDateFrom);
	        
	        SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
	        
	        NumberFormat nf  = new DecimalFormat("$#,##0");
	        
	        // 繳費截止日期查詢條件設定
	        if (null != payDueFrom)
	        {
	            // 將時分秒設定在00:00:00
	            payDueFrom = DateUtil.getBeginOfDateTime(payDueFrom);
	        }

	        if (null != payDueTo)
	        {
	            // 將時分秒設定在23:59:59
	            payDueTo = DateUtil.getEndOfDateTime(payDueTo);
	        }
	        
	        // 銷帳日期查詢條件設定
	        if (null != writeOffDateFrom)
	        {
	            // 將時分秒設定在00:00:00
	            writeOffDateFrom = DateUtil.getBeginOfDateTime(writeOffDateFrom);
	        }

	        if (null != writeOffDateTo)
	        {
	            // 將時分秒設定在23:59:59
	            writeOffDateTo = DateUtil.getEndOfDateTime(writeOffDateTo);
	        }
	        
	        // 實際繳款日期查詢條件設定
	        if (null != txDateFrom)
	        {
	            // 將時分秒設定在00:00:00
	            txDateFrom = DateUtil.getBeginOfDateTime(txDateFrom);
	        }

	        if (null != txDateTo)
	        {
	            // 將時分秒設定在23:59:59
	            txDateTo = DateUtil.getEndOfDateTime(txDateTo);
	        }
	        
	        Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

	        CreationHelper createHelper = wb.getCreationHelper();

	        Map<String, CellStyle> styles = createStyles(wb);

	        Sheet sheet = wb.createSheet("Timesheet");
	        PrintSetup printSetup = sheet.getPrintSetup();
	        printSetup.setLandscape(true);
	        sheet.setFitToPage(true);
	        sheet.setHorizontallyCenter(true);


	        // 行數
	        int rownum = 0;

	        //title row
	        Row titleRow = sheet.createRow(rownum++);
	        titleRow.setHeightInPoints(33);
	        Cell titleCell = titleRow.createCell(0);
	        titleCell.setCellValue("繳款狀況明細表");
	        titleCell.setCellStyle(styles.get("title"));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$W$1"));

	        // printDate row
	        Row printDateRow = sheet.createRow(rownum++);
	        printDateRow.setHeightInPoints((float) 22.5);
	        Cell printDateCell = printDateRow.createCell(15);
	        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	        printDateCell.setCellValue("製表日期/時間：" + dateFormat.format(Calendar.getInstance().getTime()));
	        printDateCell.setCellStyle(styles.get("title2"));
	       //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
	       //合併儲存格
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$P$2:$W$2"));
	        
	        // condition row 帳單業者名稱
	        Row conditionRow = sheet.createRow(rownum++);
	        conditionRow.setHeightInPoints((float) 22.5);
	        Cell conditionGroupNameCell = conditionRow.createCell(0);
	        conditionGroupNameCell.setCellValue("帳單業者："+dbGroup.getGroupName());
	        conditionGroupNameCell.setCellStyle(styles.get("title2"));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$E$3"));

	        // condition row 帳單業者代號
	        Cell conditionGroupNoCell = conditionRow.createCell(5);
	        conditionGroupNoCell.setCellValue("帳單業者代號 (統編+序號)："+payment.getGroup().getGroupNo());
	        conditionGroupNoCell.setCellStyle(styles.get("title2"));
	    
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$F$3:$I$3"));
	      
	        // condition row 資料期間     
	      //  Cell conditionDataPeriodCell = conditionRow.createCell(14);
	        Cell conditionDataPeriodCell = conditionRow.createCell(15);
	        
	        String sDataDuring = "";
	        if(null != txDateFrom){
	        	sDataDuring += "(1).繳費日期" + dateFormat1.format(txDateFrom)+ "~" +dateFormat1.format(txDateTo);        	
	        }
	        if (null != writeOffDateFrom){
	        	sDataDuring += "(2).銷帳日期" + dateFormat1.format(writeOffDateFrom)+ "~" +dateFormat1.format(writeOffDateTo);
	        }
	        if (null != payDueFrom){
	        	sDataDuring += "(3).繳費期限" + dateFormat1.format(payDueFrom)+ "~" +dateFormat1.format(payDueTo);	
	        }
	        conditionDataPeriodCell.setCellValue("資料期間：" + sDataDuring );
	        
	        conditionDataPeriodCell.setCellStyle(styles.get("title2"));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$P$3:$W$3"));
	               
	       //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	       // sheet.addMergedRegion(CellRangeAddress.valueOf("$O$3:$Q$3"));
	       
	        // condition row 檔案名稱     
	     //   Cell conditionFileNameCell = conditionRow.createCell(17);
	     //   conditionFileNameCell.setCellValue("檔案名稱：QAV_20101201.TXT");
	      //  conditionFileNameCell.setCellStyle(styles.get("title2"));;
	     //   sheet.addMergedRegion(CellRangeAddress.valueOf("$R$3:$T$3"));

	        //明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。
	        Row sumaryRow = sheet.createRow(rownum++);
	        sumaryRow.setHeightInPoints((float) 22.5);
	        Cell sumaryCell = sumaryRow.createCell(0);
	        sumaryCell.setCellValue("明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。");
	        sumaryCell.setCellStyle(styles.get("title2"));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$4:$N$4"));

	        //header row
	        Row headerRow = sheet.createRow(rownum++);
	        headerRow.setHeightInPoints((float) 34.5);
	        Cell headerCell;
	        for (int i = 0; i < titles.length; i++) {
	            headerCell = headerRow.createCell(i);
	            headerCell.setCellValue(titles[i]);
	            headerCell.setCellStyle(styles.get("header"));
	        }


	        List<Object> sqlArgs = new ArrayList<Object>();
	        String sql = getDelaySql(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo, sqlArgs,isSchool, delayCount);
	        logger.debug("匯出EXCEL SQL:{}", sql);
	        logger.debug("匯出EXCEL 參數:{}", sqlArgs);
	        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
	        XlsResultSetExtractor extrator = new XlsResultSetExtractor(sheet, rownum, styles, dataSource);
	        Object nullObject = jdbcTemplate.query(sql, sqlArgs.toArray(), extrator);        

	        rownum = extrator.getRownum();

	        Map<String, Long> calculateMap = extrator.getCalculateMap();
	        sumaryCell.setCellValue("明細小計：已繳款 "+calculateMap.get("Paid")+"筆，金額 "+nf.format(calculateMap.get("Total"))
	                        +"元 / 未繳款 "+calculateMap.get("unPaid")
	                        +"筆，金額 "+((Long)calculateMap.get("TotalUnPayValue").longValue()<0 ? 0 : nf.format(calculateMap.get("TotalUnPayValue")))+"元 。");
	        
	        // adjust the column widths
	        int colCount = 0;
	        //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題 
	        //titles.length == 欄位數
	        while (colCount <= titles.length) 
	        {
	            sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
	        }
	        //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	        String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
	        logger.debug("[繳款狀況明細表]暫存目錄:{}", tempDownloadFilePath);
	        File directory = new File(tempDownloadFilePath);             
	        if(!directory.exists())
	        {
	            FileUtils.forceMkdir(directory);
	        }
	        
	        String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-繳款狀況明細表.xls";
	        logger.debug("[繳款狀況明細表]:{}", fileFullName);
	        
	        FileOutputStream out;
	        out = new FileOutputStream(fileFullName);
	        wb.write(out);
	        out.flush();
	        out.close();
	        
	        return fileFullName;
	}
	
    /**
     * 取得包含判斷逾期查詢的sql
     * @return
     */
    private String getDelaySql(Payment payment, FormatDefinition formatDefinition
                    , Date payDueFrom, Date payDueTo
                    , Date writeOffDateFrom, Date writeOffDateTo
                    , Date txDateFrom, Date txDateTo
                    , List<Object> args,boolean isSchool, int delayCount) 
    {
        args.clear();
        String queryId="payer_id";
		if(isSchool)
			queryId="STUDENT_ID";
        StringBuffer sql = new StringBuffer();

        sql.append(" SELECT");
        sql.append(" PAYMENT.PAYMENT_PK");
        sql.append(" , FORMAT_DEFINITION.FORMAT_DEF_PK");
        sql.append(" , PAYMENT.STUDENT_ID");
        sql.append(" , PAYMENT.PAYER_ID ");//客戶編號
        sql.append(" , PAYMENT.VIRTUAL_ACCOUNT ");//繳款帳號
        sql.append(" , PAYMENT.PAYER_NAME ");//客戶名稱
        sql.append(" , PAYMENT.PAY_DUE ");//繳款截止日
        sql.append(" , PAYMENT.PAYMENT_STATUS ");//繳款狀況
        sql.append(" , PAYMENT.WRITE_OFF_PAYMENT_PK");
        sql.append(" , WRITE_OFF_PAYMENT.CHANNEL ");//繳款通路
        sql.append(" , WRITE_OFF_PAYMENT.TX_DATE ");//繳款交易日
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_DATE ");//繳款核銷日
        //sql.append(" -- 超商代收類別");
        //sql.append(" -- 代收手續費(超商)");
        //sql.append(" -- 帳務處理費(遠銀)");
        sql.append(" , PAYMENT.TOTAL_AMOUNT ");//應繳金額(1)
        sql.append(" , PAYMENT.TOTAL_LOANABLE_AMOUNT ");//應繳金額(2)
        sql.append(" , WRITE_OFF_PAYMENT.AMOUNT ");//實繳金額(1/2)
        /* 銷帳代碼 */
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_NO");
        /* 銷帳原因 */
        sql.append(" , WRITE_OFF_PAYMENT.WRITE_OFF_NOTE");
        /* 備註 */
        sql.append(" , PAYMENT.PAYMENT_NOTE_3 ");
        /* 註銷時間 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_DATE ");
        /* 註銷代碼 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_NO ");
        /* 註銷原因 */
        sql.append(" , PAYMENT_CANCELED.CANCEL_REASON ");
        sql.append(" , PAYMENT.BARCODE_STORES_1");
        sql.append(" , PAYMENT.BARCODE_STORES_1_LOAN");
        sql.append(" , APP_GROUP.GROUP_NO");
        sql.append(" , APP_GROUP.GROUP_NAME");
        sql.append(" , APP_GROUP.GROUP_TYPE");
        sql.append(" , APP_GROUP.BANK_AMOUNT");
        sql.append(" , APP_GROUP.POST_AMOUNT");       
        sql.append(" , APP_GROUP.ATM_AMOUNT");
        sql.append(" , APP_GROUP.REMITTANCE_AMOUNT");
        sql.append(" , APP_GROUP.OTHER_AMOUNT");  
        sql.append(" , APP_GROUP.CHAINSTORE_AMOUNT");
        sql.append(" , APP_GROUP.POST_LEVEL1");
        sql.append(" , APP_GROUP.POST_LEVEL2");
        sql.append(" , APP_GROUP.POST_LEVEL3");
        sql.append(" , APP_GROUP.SELF_AMOUNT "); /* 20120828 自行繳款之「帳務處理費」計算   */
        sql.append(" , CODE_TABLE_CHANNEL.CODE_NAME AS CHANNEL_NAME"); //繳款通路中文
        sql.append(" , CODE_TABLE_PAYMENT_STATUS.CODE_NAME AS PAYMENT_STATUS_NAME"); //繳款通路中文
        sql.append(" FROM PAYMENT LEFT JOIN WRITE_OFF_PAYMENT ON PAYMENT.WRITE_OFF_PAYMENT_PK = WRITE_OFF_PAYMENT.WRITE_OFF_PAYMENT_PK");
        /* 註銷表 */
        sql.append(" LEFT JOIN PAYMENT_CANCELED ON PAYMENT.PAYMENT_CANCELED_PK = PAYMENT_CANCELED.PAYMENT_CANCELED_PK ");
        sql.append(" INNER JOIN APP_GROUP ON PAYMENT.GROUP_PK = APP_GROUP.GROUP_PK");
        sql.append(" INNER JOIN UPLOAD_FILE ON PAYMENT.UPLOAD_FILE_PK = UPLOAD_FILE.UPLOAD_FILE_PK");
        sql.append(" INNER JOIN FORMAT_DEFINITION ON UPLOAD_FILE.FORMAT_DEF_PK = FORMAT_DEFINITION.FORMAT_DEF_PK ");
        sql.append(" LEFT JOIN CODE_TABLE CODE_TABLE_CHANNEL ON CODE_TABLE_CHANNEL.CODE_KIND='CHANEL' AND CODE_TABLE_CHANNEL.CODE_VALUE = WRITE_OFF_PAYMENT.CHANNEL");
        sql.append(" LEFT JOIN CODE_TABLE CODE_TABLE_PAYMENT_STATUS ON CODE_TABLE_PAYMENT_STATUS.CODE_KIND='PAYMENT_STATUS' AND CODE_TABLE_PAYMENT_STATUS.CODE_VALUE = PAYMENT.PAYMENT_STATUS");
        /* 條件 */
		sql.append(" where 1=1 ");
		if(delayCount > 0){
			sql.append(" and exists (");
		}else{
			sql.append(" and not exists (");
		}
		sql.append(" select  count(").append(queryId).append(") as count    from ");
		sql.append("(");
		sql.append("select  ").append(queryId).append(",");		
		sql.append("(CASE   pp.payment_Status ");
		sql.append(" WHEN 2");
		sql.append(" THEN");
		sql.append("   	 (case   ");
		sql.append(" WHEN   convert(varchar, pp.pay_Due,112 )<CONVERT(varchar, getdate(), 112)   THEN 6 ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END) ");
		sql.append(" else ");
		sql.append(" payment_Status ");
		sql.append(" END ");
		sql.append(") as payment_Status ");
		sql.append("from dbo.PAYMENT pp ");
		sql.append("where payment_Status in('2','6') ");
		/* 屬於哪一個群組 */
		if (null != payment.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != payment.getGroup().getGroupPk()){
				sql.append("and pp.group_pk = ? ");
				 args.add(payment.getGroup().getGroupPk());
			}
		}
		sql.append("and ").append(queryId).append(" is not null ");
		sql.append("");
		sql.append(") a ");
		sql.append("where payment_Status=6");
		sql.append("and a.").append(queryId).append("=PAYMENT.").append(queryId).append(" ");
		sql.append("group by  payment_Status  ,").append(queryId).append(" ");
		sql.append("having  count(").append(queryId).append(")  >= ").append(delayCount).append(" ");
		sql.append(")	 ");

        if (null != payment)
        {
            if (StringUtils.isNotEmpty(payment.getVirtualAccount()))
            {
                sql.append("AND PAYMENT.VIRTUAL_ACCOUNT LIKE ? ");
                args.add(payment.getVirtualAccount() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getPayerName()))
            {
                sql.append("AND PAYMENT.PAYER_NAME LIKE ? ");
                args.add(payment.getPayerName() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getPayerId()))
            {
                sql.append("AND PAYMENT.PAYER_ID LIKE ? ");
                args.add(payment.getPayerId() + "%");
            }

            if (StringUtils.isNotEmpty(payment.getStudentId()))
            {
                sql.append("AND PAYMENT.STUDENT_ID LIKE ? ");
                args.add(payment.getStudentId() + "%");
            }

            if (null != payment.getPayDue()) //單一日期
            {
                sql.append("AND PAYMENT.PAY_DUE between ? and ? ");
                Date from = DateUtil.getBeginOfDateTime(payment.getPayDue());
                Date to = DateUtil.getEndOfDateTime(payment.getPayDue());
                args.add(from);
                args.add(to);
            }
        }        


        // 繳費截止日期
        if (null != payDueFrom && null != payDueTo)
        {
            sql.append("AND PAYMENT.PAY_DUE between ? and ? ");
            args.add(payDueFrom);
            args.add(payDueTo);
        }
        else if (null != payDueFrom) // 只有起
        {
            sql.append("AND PAYMENT.PAY_DUE >= ? ");
            args.add(payDueFrom);
        }
        else if (null != payDueTo) // 只有迄
        {
            sql.append("AND PAYMENT.PAY_DUE <= ? ");
            args.add(payDueTo);
        }


        // 銷帳日期
        // 有起迄
        if (null != writeOffDateFrom && null != writeOffDateTo)
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE between ? and ? ");
            args.add(writeOffDateFrom);
            args.add(writeOffDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE between ? and ? ) ");
            args.add(writeOffDateFrom);
            args.add(writeOffDateTo);

        }
        else if (null != writeOffDateFrom) // 只有起
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE >= ? ");
            args.add(writeOffDateFrom);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE >= ? ) ");
            args.add(writeOffDateFrom);
        }
        else if (null != writeOffDateTo) // 只有迄
        {
            sql.append("AND (WRITE_OFF_PAYMENT.WRITE_OFF_DATE <= ? ");
            args.add(writeOffDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
            /* 註銷日期 */
            sql.append("OR PAYMENT_CANCELED.CANCEL_DATE <= ? ) ");
            args.add(writeOffDateTo);
        }

        // 實際繳款日期
        // 有起迄
        if (null != txDateFrom && null != txDateTo)
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE between ? and ? ");
            args.add(txDateFrom);
            args.add(txDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);

        }
        else if (null != txDateFrom) // 只有起
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE >= ? ");
            args.add(txDateFrom);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
        }
        else if (null != txDateTo) // 只有迄
        {
            sql.append("AND WRITE_OFF_PAYMENT.TX_DATE <= ? ");
            args.add(txDateTo);
            sql.append("AND WRITE_OFF_PAYMENT.WRITE_OFF_STATUS =?  "); //正常銷帳
            args.add(WriteOffPayment.WRITEOFF_STATUS_OK);
        }

        // 收款類別
        if (null != formatDefinition)
        {
            if (null != formatDefinition.getFormatDefinitionPk())
            {
                sql.append("AND FORMAT_DEFINITION.FORMAT_DEF_PK = ? ");
                args.add(formatDefinition.getFormatDefinitionPk());
            }
        }
        

        /* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */
        sql.append("AND PAYMENT.PAYMENT_STATUS in (2,6) ");
            
    

        logger.debug("sql.toString() {}",sql.toString());
        return sql.toString();
        
    }



	
}
